﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using OfficeOpenXml;
using DatabaseCore;
using SystemCore;
using SystemCore.Helper;


namespace Helper
{
    public static class ExcelHelper
    {
        private const string C_SHEET_NAME = "Check";
        public static bool GetOrderData(string filePath, string orderNo, string createBy, out tbl_Order order, out List<tbl_OrderDetail> orderDetails, out string matDuplicate)
        {
            matDuplicate = "";
            order = new tbl_Order();
            orderDetails = new List<tbl_OrderDetail>();
            FileInfo existingFile = new FileInfo(filePath);
            ExcelWorksheet worksheet;
            using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
            {

                try
                {
                    try
                    {
                        worksheet = xlPackage.Workbook.Worksheets[C_SHEET_NAME];
                    }
                    catch (Exception)
                    {

                        worksheet = xlPackage.Workbook.Worksheets[1];
                    }
                    

                    #region Get Header Order

                    order.Status = (int)EOrderStatus.Order;
                    order.RefOrderNo = worksheet.Cells[5, 3].Value.ToString();
                    order.OrderDate = DateTime.FromOADate(Convert.ToDouble((worksheet.Cells[5, 5].Value)));
                    order.InvoiceNo = worksheet.Cells[5, 10].Value.ToString();
                    order.PortOfShipment = worksheet.Cells[6, 3].Value.ToString();
                    order.PortOfDestination = worksheet.Cells[6, 8].Value.ToString();
                    order.CreateBy = createBy;
                    order.CreateDate = DateTime.Now;
                    order.UpdateBy = createBy;
                    order.UpdateDate = DateTime.Now;
                    #endregion

                    #region Details Order
                    int rowStart = 14;
                    int i = 0;
                    string itemNo = "0";

                    while (!string.IsNullOrEmpty(itemNo))
                    {
                        tbl_OrderDetail orderDetail = new tbl_OrderDetail();

                        if (worksheet.Cells[rowStart, 1].Value == null)
                            break;

                        itemNo = worksheet.Cells[rowStart, 1].Value.ToString();

                        if (string.IsNullOrEmpty(itemNo))
                            break;

                        orderDetail.OrderNo = orderNo;
                        orderDetail.ItemNo = Convert.ToInt32(itemNo);
                        //orderDetail.ItemId = worksheet.Cell(rowStart, 2).Value; no need Item id use mat id insteel
                        //orderDetail.ItemName;
                        //orderDetail.ProductId = worksheet.Cells[rowStart, 4].Value.ToString();
                        //orderDetail.ProductName;
                        orderDetail.Qty = Convert.ToInt32(worksheet.Cells[rowStart, 5].Value);
                        orderDetail.ReceivedQty = 0;
                        orderDetail.RemainingQty = orderDetail.Qty;// Convert.ToInt32(worksheet.Cells[rowStart, 5].Value);
                        orderDetail.UnitPrice = 0;// Convert.ToDecimal(worksheet.Cells[rowStart, 6].Value);
                        orderDetail.Amount = 0;//Convert.ToDecimal(worksheet.Cells[rowStart, 7].Value);
                        orderDetail.PcsPerBox = 0;// Convert.ToInt32(worksheet.Cells[rowStart, 8].Value);
                        orderDetail.Package = 0;// Convert.ToInt32(worksheet.Cells[rowStart, 9].Value);
                        orderDetail.WeightPerUnit = 0;// Convert.ToDecimal(worksheet.Cells[rowStart, 10].Value.ToString().Replace("E-2", ""));
                        orderDetail.Weight = 0;// Convert.ToDecimal(worksheet.Cells[rowStart, 11].Value);
                        orderDetail.MaterialNo = worksheet.Cells[rowStart, 12].Value.ToString(); //sapDao.GetMaterialNo(productCode);
                        orderDetail.ItemId = orderDetail.MaterialNo;
                        //orderDetail.Plant = values[record, "N"].Value2.ToString().Trim(); //GetFromSAP();

                        orderDetail.CreateBy = createBy;
                        orderDetail.CreateDate = DateTime.Now;
                        orderDetail.UpdateBy = createBy;
                        orderDetail.UpdateDate = DateTime.Now;

                        if (orderDetails.Exists(x => x.MaterialNo == orderDetail.MaterialNo))
                        {
                            matDuplicate = orderDetail.MaterialNo;
                            return false;
                        }

                        orderDetails.Add(orderDetail);

                        rowStart++;
                        i++;
                    }

                    //order.TotalQty = Convert.ToInt32(worksheet.Cells[rowStart, 11].Value);
                    //order.TotalPackage = Convert.ToInt32(worksheet.Cells[rowStart + 1, 11].Value);
                    //order.TotalAmount = Convert.ToDecimal(worksheet.Cells[rowStart + 2, 11].Value);
                    //order.TotalWeight = Convert.ToDecimal(worksheet.Cells[rowStart + 3, 11].Value);

                    #endregion

                    return true;
                }
                catch (Exception ex)
                {
                    ExceptionHelper.LogAppError(ex.Message + " strack : " + ex.StackTrace);
                    return false;
                }
                // get the first worksheet in the workbook
                
            }
        }

        public static byte[] ExportToExcel(List<object> datas)
        {

            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
                ws.Cells["A1"].LoadFromCollection(datas, true);

                return pck.GetAsByteArray();
            }

        }
        
    }
}
